﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Reflection;
using Dapper;
using Newtonsoft.Json;
using API_Service.DBHelper;
using System.Configuration;
using System.Text;

namespace API_Service.BizLogic.WorkOrder
{
    public class WOService : BaseLogicService
    {
        public WOService() { }

        public ExecutionResult<dynamic> GetMOList()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = " select mo_no,mo_type,target_qty,mo_create_time,mo_target_time,sku_no,bom_no,route_code,default_line,input_group,output_group,pack_input_group,pack_output_group,mo_status,remarks from r_mo_base_t " +
                                         "where mo_create_time > GETDATE() - 6000 ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetMOByID(string mo)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = " select mo_no,mo_type,target_qty,mo_create_time,mo_target_time,sku_no,bom_no,route_code,default_line,input_group,output_group,pack_input_group,pack_output_group,mo_status,remarks from r_mo_base_t " +
                                         "where mo_no=@mo ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql,new { mo=mo}).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetNewMONumber()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = " select 'TYPE1' as type,CONVERT(varchar(100), GETDATE(), 112)+'M'+max(RIGHT(REPLICATE('0',2)+CAST(cast(right(mo_no,2) as int)+1 AS varchar(10)),2)) as newmo from r_mo_base_t " +
                                         "where substring(mo_no,9,1) ='M' and left(mo_no,8) = CONVERT(varchar(100), GETDATE(), 112) " +
                                         "union select 'TYPE2' as type,CONVERT(varchar(100), GETDATE(), 112)+'M01' ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql).ToArray();
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> SaveMOData(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(viewModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                conn.Open();

                IDbTransaction transaction = conn.BeginTransaction();

                try
                {
                    string sql_select = " select  mo_no from r_mo_base_t where mo_no=@mo ";

                   string sql_insert = " insert into r_mo_base_t(mo_no,mo_type,target_qty,mo_create_time,mo_target_time,sku_no,bom_no,route_code,default_line,input_qty,output_qty,input_group,output_group,pack_input_group,pack_output_group,mo_status,remarks) " +
                                                     "values(@mo,@motype,@targetqty,@mocreatedate,@motargetdate,@sku,@bomno,@route,@line,@i_qty,@o_qty,@inputgroup,@outputgroup,@p_inputgroup,@p_outputgroup,@status,@remarks) ";

                    string sql_update = " update r_mo_base_t set mo_type=@motype,target_qty=@targetqty,mo_create_time=@mocreatedate,mo_target_time=@motargetdate," +
                                                       "sku_no=@sku,route_code=@route,default_line=@line,input_qty=@i_qty,output_qty=@o_qty,input_group=@inputgroup, output_group=@outputgroup,mo_status=@status,remarks=@remarks " +
                                                       "where mo_no=@mo ";

                    int cnt = conn.Query(sql_select, new { mo = (string)vm.mo }, transaction).Count();

                    var model = new
                    {
                        mo = (string)vm.vmo,
                        motype = (string)vm.vmotype,
                        targetqty = (int)vm.vqty,
                        mocreatedate = (string)vm.vcreatedate,
                        motargetdate = (string)vm.voutdate,
                        sku = (string)vm.vsku,
                        bomno=(string)vm.vbomno,
                        route = (string)vm.vroute,
                        line = (string)vm.vline,
                        i_qty = 0,
                        o_qty = 0,
                        inputgroup = (string)vm.vinputgroup,
                        outputgroup = (string)vm.voutputgroup,
                        p_inputgroup = (string)vm.vpackinputgroup,
                        p_outputgroup = (string)vm.vpackoutputgroup,
                        status = "1",
                        remarks = "",

                    };

                    if (cnt > 0)
                    {
                        //update
                        result.ExecuteCount = conn.Execute(sql_update, model, transaction);
                        result.Message = "MO数据更新完成.";

                    }
                    else
                    {
                        //insert
                        result.ExecuteCount = conn.Execute(sql_insert, model, transaction);
                        result.Message = "MO数据插入完成.";
                    }                   

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();

                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> DeleteMO(string mo)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                conn.Open();

                try
                {
                    string sql_select = " delete from r_mo_base_t where mo_no=@mo ";

                   result.ExecuteCount = conn.Execute(sql_select, new { mo =mo });
                    result.Status = true;
                    result.Message = "MO 删除成功..";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> ExpandOrderSN(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(viewModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                conn.Open();

                IDbTransaction transaction = conn.BeginTransaction();

                string sql_check = " select a.mo_no,count(b.serial_number) act_qty, a.target_qty from r_mo_base_t a, r_wip_tracking_t b where a.mo_no = b.mo_number and a.mo_no=@mo group by a.mo_no,a.target_qty ";

                string sql = "select mo_no,target_qty from r_mo_base_t where mo_no=@mo";

                try
                {
                    dynamic[] mo_res = conn.Query(sql_check, new { mo = (string)vm.mo }, transaction).ToArray();

                    if (mo_res.Length > 0 && mo_res[0].act_qty >= mo_res[0].target_qty)
                    {
                        result.Status = false;
                        result.Message = "MO：" + vm.mo + "  已经生成SN.";
                        return result;
                    }

                    dynamic res = conn.Query(sql, new { mo= (string)vm.mo }, transaction).ToArray()[0];

                    string mo = res.mo_no;

                    int qty = (int)res.target_qty;

                    int seq_start = 1;

                    string sql_insert = "insert into r_wip_tracking_t(serial_number,mo_number,model_name,line_name,group_name,station_name," +
                                                     "in_station_time,error_flag,sku_no,bom_no,shipping_sn,pallet_no,next_station,po_no,po_line,route_code,create_time,create_user) " +
                                                     "values(@serial_number,@mo_number,@model_name,@line_name,@group_name,@station_name," +
                                                     "@in_station_time,@error_flag,@sku_no,@bom_no,@shipping_sn,@pallet_no,@next_station,@po_no,@po_line,@route_code,@create_time,@create_user)";


                    var SNs = new List<dynamic>();

                    StringBuilder strSN = new StringBuilder();

                    while(seq_start <= qty)
                    {
                        strSN.Clear();
                        strSN.Append(mo);
                        strSN.Append(seq_start.ToString().PadLeft(5, '0'));

                        SNs.Add(new
                        {
                            serial_number = strSN.ToString(),
                            mo_number = mo,
                            model_name = (string)vm.sku_no,
                            line_name = (string)vm.line_name,
                            group_name = (string)vm.group_name,
                            station_name = (string)vm.station_name,
                            in_station_time = DateTime.Now,
                            error_flag = '0',
                            sku_no = (string)vm.sku_no,
                            bom_no = (string)vm.bom_no,
                            shipping_sn = "N/A",
                            pallet_no = "N/A",
                            next_station = "N/A",
                            po_no = "N/A",
                            po_line = "N/A",
                            route_code = (string)vm.route_code,
                            create_time = DateTime.Now,
                            create_user = (string)vm.create_user

                        });

                        seq_start++;
                    }

                    result.ExecuteCount = conn.Execute(sql_insert, SNs, transaction);

                    string sql_update_mo = "update r_mo_base_t  set mo_status='2' where mo_no=@mo"; //Status: 1/开立  2/上线   3/完成

                    conn.Execute(sql_update_mo, new { mo = mo }, transaction);

                    result.Message = "MO: " + mo + "  " + result.ExecuteCount + "序列号展开完成.";

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();

                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.ExecuteCount = -1;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> SNPrintDataSave(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = JsonConvert.DeserializeObject<dynamic>(viewModel);

            using (IDbConnection conn = SqlCreator.Create())
            {
                conn.Open();

                IDbTransaction transaction = conn.BeginTransaction();

                string sql_insert = @" insert into r_barcode_print_t values
                                                        (@sn,@code_type,@batch_number,@pn,@print_type,@isdefect,@print_factory,
                                                        @create_by,@create_date) ";

                try
                {
                    List<dynamic> printlist = new List<dynamic>();

                    foreach (var data in vm)
                    {
                        printlist.Add(
                            new
                            {   sn = (string)data.sn,
                                code_type = (string)data.code_type,
                                batch_number = (string)data.batch_number,
                                pn = (string)data.pn,
                                print_type = (string)data.print_type,
                                isdefect = (string)data.isdefect,
                                print_factory = (string)data.print_factory,
                                create_by = (string)data.create_by,
                                create_date = (DateTime)data.create_date}
                            );
                    }

                    int exe_count = conn.Execute(sql_insert, printlist, transaction);

                    result.Status = true;

                    result.Message = "MO: " + vm[0].batch_number + "  SN条码打印完成.";

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();

                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.ExecuteCount = -1;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetMOSNList(string mo)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" select distinct c.*,case when d.barcode is not null then 'YES' else 'NO' end isprint from 
                                                (select b.serial_number,a.mo_no,a.sku_no from r_mo_base_t a, r_wip_tracking_t b 
                                                where a.mo_no = b.mo_number and a.mo_no=@mo) c 
                                                left join r_barcode_print_t d on c.serial_number = d.barcode ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { mo = mo }).ToArray();

                    result.Status = true;
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetMOSNListBySN(string sn)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" select distinct c.*,case when d.barcode is not null then 'YES' else 'NO' end isprint from 
                                                (select b.serial_number,a.mo_no,a.sku_no from r_mo_base_t a, r_wip_tracking_t b 
                                                where a.mo_no = b.mo_number and b.serial_number=@sn) c 
                                                left join r_barcode_print_t d on c.serial_number = d.barcode ";

                    conn.Open();

                    result.Datas = conn.Query<dynamic>(sql, new { sn = sn }).ToArray();

                    result.Status = true;
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);

                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }


    }
}